package test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * 
 * @author shefali
 * In all the functions of this class a connection to the database is achieved via a DriverManager class
 * Once the JDBC drivers are loaded, getConnection method is applied to the Driver Manager class using the
 * Appropriate url and username, password
 * 
 * Prepared statement is used to send SQL statements to the database server since it is more efficient than the
 * Statement object
 * The ResultSet class is used to access the table of data generated by executing the sql queries
 *
 */
public class PaticipantDataAccess {	
	
	/**
	 * 
	 * @param fName
	 * @param lName
	 * @param title
	 * @param organization
	 * @param address
	 * @param city
	 * @param state
	 * @param zip
	 * @param country
	 * @param phone
	 * @param fax
	 * @param email
	 * @param partOf
	 * @return participantId generated using Oracle sequences.
	 * @throws SQLException
	 * @throws IOException
	 *  Saves all the personal details of the participant when the participant registers for any conference the first time.
	 * A returning participant is identified by email id i.e if the email id already exists in the database that means the participant has at some point
	 * registered for some tutor/Mentor Conference.In this case the participantId which was generated the first time the user registered 
	 *  is fetched from the database and the corresponding column attributes are updated.
	 *   The participant id is generated for a new user using ORACLE sequences and the other relevant data is inserted in the Participant table
	 *   The function returns the already existing or the newly generated participantId
	 */
	public int saveParticipantDetails(String fName, String lName, String title,
			String organization, String address, String city, String state,
			int zip, String country, String phone, String fax, String email,
			String partOf) throws SQLException, IOException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rset;
		int participationId = 0;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");
			String query = "Select participantId" + " from participant"
					+ " where email = " + "'" + email + "'";
			stmt = conn.createStatement();
			rset = stmt.executeQuery(query);
			if(rset.next())
			{
				 participationId = rset.getInt(1);
				
				String query1 = "Update participant set fName = ?, lName= ?, title = ?, company = ?," +
						"address = ?, city = ?, state = ?, zipcode = ?, country = ?, phone = ?, fax =?, partOf = ? where email = ? and participantid = ?";
				
				pstmt = conn.prepareStatement(query1);
				pstmt.setString(1, fName);
				pstmt.setString(2,lName);
				pstmt.setString(3,title);
				pstmt.setString(4, organization);
				pstmt.setString(5, address);
				pstmt.setString(6, city);
				pstmt.setString(7,state );
				pstmt.setInt(8, zip);
				pstmt.setString(9, country);
				pstmt.setString(10, phone);
				pstmt.setString(11, fax);
				pstmt.setString(12, partOf);
				pstmt.setString(13, email);
				pstmt.setInt(14, participationId);
				
				pstmt.executeUpdate();
			}
			else
			{
				String query2 = "Insert into participant values(sequence_ParticipantId.nextval,?,?,?, ?, ?, ?,?, ?,?,?,?,?,?)";
		
		pstmt = conn.prepareStatement(query2);
		pstmt.setString(1, fName);
		pstmt.setString(2,lName);
		pstmt.setString(3,title);
		pstmt.setString(4, organization);
		pstmt.setString(5, email);
		pstmt.setString(6, address);
		pstmt.setString(7, city);
		pstmt.setString(8,state);
		pstmt.setInt(9, zip);
		pstmt.setString(10, country);
		pstmt.setString(11, phone);
		pstmt.setString(12, fax);
		pstmt.setString(13, partOf);
		
		 pstmt.executeUpdate();
		 String query3 = "select participantid from Participant where email = ?";
			pstmt = conn.prepareStatement(query3);
			pstmt.setString(1, email);
			rset = pstmt.executeQuery();
			}
			while(rset.next())
			{
				Participant p = new Participant();
				p.setParticipantId(rset.getInt(1));
				participationId = p.getParticipantId();
			}	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return participationId;
	}
	
	/**
	 * 
	 * @param participationId
	 * @param conferenceId
	 * @param lunchOption
	 * @param attendeeList
	 * @return registraionId generated using Oracle sequences.
	 * @throws SQLException
	 * @throws IOException
	 * Saves all the information specific to registration for a particular conference in the Registration table that contains the
	 * participantId allocated to every participant as the foreign key
	 * 
	 */
	
	
	public int saveRegistrationDetails(int participationId,int conferenceId, String lunchOption, String attendeeList) throws SQLException,IOException
	
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset;
		int registrationId = 0;
		try
		{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String user, pass;
		user = "skakodkar";
		pass = "p73505";
		conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
				+ pass + "@oraserver.cs.luc.edu:1521:cs");
		String query = "Insert into registration values(sequence_RegistrationId.nextval,?,?,?,?)";
		pstmt = conn.prepareStatement(query);
		pstmt.setInt(1,participationId);
		pstmt.setInt(2, conferenceId);
		pstmt.setString(3,lunchOption);
		pstmt.setString(4, attendeeList);
		 pstmt.executeUpdate();
		
		String query3 = "select registrationId from Registration where participantId = ? and conferenceId = ?";
		pstmt = conn.prepareStatement(query3);
		pstmt.setInt(1, participationId);
		pstmt.setInt(2, conferenceId);
		rset = pstmt.executeQuery();
		
		while(rset.next())
		{
			registrationId = rset.getInt(1);
		}
		
		
	}catch (Exception e)
	{
		e.printStackTrace();
	}finally{
		conn.close();
	}
		
		return registrationId;
	}
	
	
	/**
	 * 
	 * @param registrationId
	 * @param conferenceId
	 * @param attendOn
	 * @param date
	 * @throws SQLException
	 * @throws IOException
	 *  Saves information related to the participant attending the conference on which days in the Attendance table
	 */
	
	public void saveAttendOnDetails(int registrationId, int conferenceId, String attendOn, String date)throws SQLException,IOException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";
			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");
			if(date == null)
			{
				String query1 = "Insert into Attendance(registrationId, attendOn) values(?,?)";
				pstmt = conn.prepareStatement(query1);
				pstmt.setInt(1,registrationId);
				pstmt.setString(2,attendOn);
				pstmt.executeUpdate();
				int x = pstmt.executeUpdate();
				System.out.println(x);
				
			}
			else
			{
				String query2 = "Insert into Attendance(attendDay1,registrationId, attendOn) values(?,?,?)";
				pstmt = conn.prepareStatement(query2);
				pstmt.setString(1,date);
				pstmt.setInt(2,registrationId);
				pstmt.setString(3,attendOn);
			int x=	pstmt.executeUpdate();
			}
			
			
		}catch (Exception e)
		{
			e.printStackTrace();
		}finally
		{
			conn.close();
		}
		
	}

}

